import time
from apis import api_routes
from flask import g, request, jsonify, abort

from models.owner.owner import Owner
from security.decoration import certification
from sqlalchemy import and_
from typing import List
from typing import Union
from sqlalchemy import text
from sqlalchemy import func
from public.local import date_type
from datetime import datetime, timedelta
from models.course.course import Course
from models.product.routes import Routes
from models.order.tasks import Tasks
from models.product.aircraft import Aircraft


@api_routes.route("/report/task", methods=["GET"])
@certification(auth=True, role=None)
def func_board_task_get_api():
    """
    机种产量统计接口，完成API开发
    http://127.0.0.1:8080/api/report/task
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "start"         string       开始时间, 默认今天时间  格式 %Y-%m-%d
    "finish"        string       结束时间，默认今天时间  格式 %Y-%m-%d
    "owner_id"      Int         默认(下拉，键值对)，可以不选, 排班名称，1白班， 2晚班
    }
    :return:
        {'status': 200, 'data': resp}
        {'status': 4001, 'msg': "选择班次不能跨天查询"}
    ---
    tags:
      - 机种产量统计
    """
    start: Union[datetime, str, None] = request.args.get("start", default=None, type=str)
    aircraft_id: int = request.args.get("aircraft_id", default=0, type=int)
    finish: Union[datetime, str, None] = request.args.get("finish", default=None, type=str)
    owner_id: int = request.args.get("owner_id", default=0, type=int)

    if owner_id not in [0, 1, 2]:
        abort(code=400)

    if start is not None and len(start) == 0:
        start = None
    if finish is not None and len(finish) == 0:
        finish = None

    if (owner_id and start is None) or (owner_id and finish is None) or (owner_id and start != finish):
        return jsonify({'status': 4001, 'msg': "选择班次不能跨天查询或未指定日期"})

    back_start = start
    back_finish = finish

    if start is not None:
        start = datetime.strptime(start, "%Y-%m-%d").strftime("%Y-%m-%d %H:%M:%S")
    if finish is not None:
        temp_date_time = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    db_owner: Union[Owner, None] = g.session.query(Owner).filter(Owner.id == owner_id).first()
    if db_owner is not None and db_owner.start <= db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        finish = "{} {}".format(back_finish, db_owner.finish.strftime("%H:%M:%S"))
    elif db_owner is not None and db_owner.start > db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        hour = db_owner.finish.hour
        temp_date_time = datetime.strptime(back_finish, "%Y-%m-%d") + timedelta(days=1) + timedelta(hours=hour)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    aircraft: dict = {}
    db_data: List[Aircraft] = g.session.query(Aircraft).order_by(Aircraft.id).all()
    for itr in db_data:
        aircraft[itr.aircraft] = itr.id

    sql_total = """
        SELECT 
            COUNT(sequence) FROM courses
        WHERE finish >= :start AND finish <= :finish AND mark > 0 AND aircraft_id = :include
    """
    sql_oks = """
        SELECT 
            COUNT(sequence) FROM courses
        WHERE finish >= :start AND finish <= :finish AND mark = 1 AND aircraft_id = :include
    """
    sql_ngs = """
        SELECT 
            COUNT(sequence) FROM courses
        WHERE finish >= :start AND finish <= :finish AND mark = 2 AND aircraft_id = :include
    """
    sql_scraps = """
        SELECT 
            COUNT(sequence) FROM courses
        WHERE finish >= :start AND finish <= :finish AND mark = 3 AND aircraft_id = :include
    """

    resp: list = []
    keys: list = list(aircraft.keys())
    for _, itr in enumerate(keys):

        parm_total: dict = {"start": start, "finish": finish, "include": aircraft.get(itr)}
        db_total = g.session.execute(text(sql_total), parm_total).fetchone()[0]

        parm_oks: dict = {"start": start, "finish": finish, "include": aircraft.get(itr)}
        db_oks: int = g.session.execute(text(sql_oks), parm_oks).fetchone()[0]

        parm_ngs: dict = {"start": start, "finish": finish, "include": aircraft.get(itr)}
        db_ngs: int = g.session.execute(text(sql_ngs), parm_ngs).fetchone()[0]

        parm_scraps: dict = {"start": start, "finish": finish, "include": aircraft.get(itr)}
        db_scraps: int = g.session.execute(text(sql_scraps), parm_scraps).fetchone()[0]

        resp.append({
            "key": itr,
            "total": db_total,
            "oks": db_oks,
            "ngs": db_ngs,
            "scraps": db_scraps
        })
    docs: dict = {
        "key": "机种名称",
        "total": "总共生产数量",
        "oks": "合格数量",
        "ngs": "不合格数量",
        "scraps": "报废数量"
    }
    return jsonify({'status': 200, 'data': resp, "docs": docs})
